﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_ListOrders]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ListOrders]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE dbo.sp_ListOrders
	(
	@orderId int = null
	)
/*
  Sample:
   sp_ListOrders       - list all orders
   sp_ListOrders 10248 - list Order with Order.ID = 10248
*/	
AS
	SET NOCOUNT ON
	
	select 
	 [OrderDetail].OrderId, 
	 [Order].OrderDate,
	 [Product].Name, 
	 [OrderDetail].Quantity
	 from [Order],[OrderDetail],[Product]
	 where
	  [Order].ID = OrderDetail.OrderId and
	  [OrderDetail].ProductId = Product.ID and
	  (@orderId is null or @orderId = [Order].ID)
GO
